Creating Roles and Defining DAX-Based Security Rules in Power BI

Course: Microsoft Power BI Training

Topic: Creating Roles and Defining DAX-Based Security Rules

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Power BI Security Roles

Power BI allows developers to control which records users can access in reports and semantic models. One of the most important security features is Row-Level Security (RLS), which restricts the rows of data visible to different users.

To implement RLS, developers create security roles and define filtering rules. These rules can be written using Data Analysis Expressions (DAX), the formula language used in Power BI for calculations, data analysis, and security filtering.

For example, a company may maintain a single sales report containing data for Mumbai, Pune, Delhi, and Bengaluru. Each regional manager should see only their own region's sales, while an authorized executive may need access to all regions.

Instead of creating separate reports for every manager, a developer can create security roles and apply DAX-based rules to restrict the data available to each user.

1.1 Learning Objectives

After completing this topic, learners should be able to:

• Understand the purpose of security roles in Power BI.
• Differentiate static and dynamic Row-Level Security.
• Create roles using Power BI Desktop.
• Write DAX expressions for security filtering.
• Use USERPRINCIPALNAME() and USERNAME() in dynamic security.
• Build user-mapping tables for managing access.
• Configure role membership in the Power BI service.
• Test, troubleshoot, and optimize DAX-based security rules.

2. What Is a Security Role?

A security role is a collection of rules that determines which rows of data a user can access in a Power BI semantic model.

Each role can contain filtering rules for one or more tables. A DAX expression evaluates the rows of a table and returns a logical result. Rows that satisfy the security condition remain visible, while rows that do not satisfy it are excluded from the user's secured view.

2.1 Example of a Security Role

Suppose a Sales table contains the following data:

If a role named WestRegion contains a rule that permits only rows where Region equals West, users assigned to that role will see the records for Amit and Priya.

2.2 Benefits of Security Roles

• Protect confidential business information.
• Provide different users with different views of the same semantic model.
• Reduce the need to maintain duplicate reports.
• Support regional, departmental, and organizational data restrictions.
• Centralize data access rules within the semantic model.
• Support scalable security management when combined with user groups and dynamic rules.

3. Understanding Row-Level Security (RLS)

Row-Level Security is a Power BI feature that filters records according to security-role rules. RLS does not remove the original records from the underlying data source; it restricts the records returned through the secured semantic model for the applicable user.

3.1 How RLS Works

1. A developer creates a security role in Power BI Desktop.
2. The developer defines one or more DAX filtering expressions.
3. The role definitions are saved with the semantic model.
4. The semantic model is published to the Power BI service.
5. Authorized users or supported groups are assigned to the role.
6. When a user accesses the secured report, the relevant rules determine which rows can be returned.

3.2 Important RLS Behavior

RLS is intended to restrict row visibility for report consumers. In the Power BI service, RLS applies to users accessing the semantic model with Viewer permissions. It does not restrict workspace Admin, Member, or Contributor users in the same manner.

Therefore, if a user must be restricted to a specific region, avoid granting that user a higher workspace role unless the broader access is intentional.

4. Types of Security Rules

Power BI security rules commonly fall into two categories: static RLS and dynamic RLS.

4.1 Static Row-Level Security

Static RLS uses a predefined condition. The rule filters records using a fixed value such as a region, department, or business unit.

For example, a WestRegion role can use the following expression:

[Region] = "West"

This rule always filters the table to rows whose Region value is West, regardless of which user is assigned to the role.

4.2 Dynamic Row-Level Security

Dynamic RLS uses the current user's identity or another authorized identity value to determine which records the user can see.

For example, a user-mapping table may associate [email protected] with West and [email protected] with North. A single dynamic role can use the signed-in user's identity to select the correct mapping.

Dynamic RLS is especially useful when an organization has many users and frequently changes employee assignments.

4.3 Static vs. Dynamic RLS

5. Creating a Security Role in Power BI Desktop

Power BI Desktop provides a Manage roles interface for defining RLS roles and their rules.

5.1 Prerequisites

• Power BI Desktop must be installed.
• A semantic model must contain the tables to be secured.
• The relevant columns and relationships must be correctly configured.
• The developer must understand which users should see which records.
• The data model should be tested with representative examples.

5.2 Step-by-Step Procedure

1. Open the Power BI Desktop file.
2. Confirm that the required tables and relationships are available in the model.
3. Select the Modeling tab in the ribbon.
4. Select Manage roles.
5. Select New to create a role.
6. Enter a descriptive role name, such as WestRegion.
7. Select the table on which the rule will be applied.
8. Switch to the DAX editor if a DAX expression is required.
9. Enter the security expression.
10. Validate the expression using the available validation control.
11. Select Save.
12. Use View as to test the role.

Important: Role names should be descriptive and must not contain commas. Users are not assigned to roles in Power BI Desktop; membership is configured in the Power BI service after publication.

6. Understanding DAX-Based Security Rules

DAX-based security rules use expressions to determine which records remain visible to a role. A row filter must evaluate to TRUE or FALSE for the relevant row context.

6.1 Basic DAX Rule Structure

[ColumnName] = "Value"

For example:

[Region] = "West"

This expression returns TRUE for rows whose Region value is West and FALSE for other rows.

6.2 Comparison Operators

6.3 Logical Operators

Logical operators allow developers to combine multiple conditions.

AND: Both conditions must be TRUE.

[Region] = "West" && [SalesAmount] > 10000

This expression permits rows in the West region where the sales amount exceeds 10,000.

OR: At least one condition must be TRUE.

[Region] = "West" || [Region] = "North"

This expression permits records from either the West or North region.

NOT: Reverses a logical condition.

NOT([Region] = "West")

This expression permits rows where the Region value is not West. If blank region values exist, test their behavior explicitly before using this rule in production.

7. Example 1: Creating a Static Regional Role

Assume that a company wants the West regional manager to see only West region sales.

7.1 Sample Dataset

7.2 DAX Security Expression

[Region] = "West"

7.3 Expected Result

The role filters the Sales table so that only records matching the West condition remain visible.

8. Example 2: Filtering Data Using Multiple Conditions

Sometimes, a user should see records only when more than one condition is satisfied.

8.1 Business Requirement

Allow a user to see West region sales transactions only when the sales amount is at least 10,000.

8.2 DAX Expression

[Region] = "West" && [SalesAmount] >= 10000

8.3 Explanation

• The first condition checks whether the region is West.
• The second condition checks whether the sales amount is at least 10,000.
• The AND operator requires both conditions to be TRUE.
• Rows that fail either condition are excluded from the secured view.

Such rules can be used for business-specific access requirements, but they should be designed carefully so that security filtering does not unintentionally hide required records.

9. Using USERPRINCIPALNAME() for Dynamic Security

USERPRINCIPALNAME() returns the current user's principal name. In common organizational Power BI scenarios, this value resembles the user's sign-in email address.

It is frequently used to match the signed-in identity against a user-mapping table.

9.1 Example User-Mapping Table

9.2 Simple Identity-Based DAX Expression

[UserEmail] = USERPRINCIPALNAME()

This expression is suitable for a table containing a UserEmail column. It filters that table to rows where UserEmail matches the current user's principal name.

It does not automatically filter a separate Sales table unless the model's relationships and security propagation are configured appropriately.

9.3 Why Dynamic RLS Is Useful

• One role can support many users.
• Users can receive different data access based on their identities.
• Changes to user assignments can be managed through the mapping data.
• It can reduce the number of security roles that administrators must maintain.

10. Building a User-Mapping Table

A user-mapping table associates each user with the data they are authorized to access. It may map users to regions, departments, territories, projects, or business units.

10.1 Example Mapping Table

UserAccess
----------------------------------
UserEmail              Region
[email protected]       West
[email protected]       North
[email protected]      South
[email protected]      West

10.2 Example Sales Table

Sales
----------------------------------
OrderID    Region    SalesAmount
101        West      50000
102        North     40000
103        South     35000
104        West      60000

10.3 Creating Relationships

1. Load both the Sales table and UserAccess table into Power BI Desktop.
2. Confirm that the region values use consistent formats.
3. Create or validate a Region dimension if it is needed for a clean data model.
4. Relate the Region dimension to Sales using the region key.
5. Configure the security mapping so the current user's permitted region filters the Region dimension and then the Sales table.
6. Validate the relationship direction and security-filter propagation.

A common design uses a Region dimension with a one-to-many relationship to Sales. The UserAccess table is filtered by the signed-in user, and the resulting region filter must reach the Region dimension through a correctly configured security path. Depending on the model, this can require a carefully designed relationship or a DAX rule on the Region table.

11. Dynamic RLS Using a Mapping Table

There are several valid dynamic RLS designs. The appropriate approach depends on the data model, relationships, and security requirements.

11.1 Filtering the UserAccess Table

One part of a dynamic security configuration can be the following role expression on the UserAccess table:

[UserEmail] = USERPRINCIPALNAME()

This filters the mapping table to the current user. A valid relationship and security-filter path must then carry the permitted region to the Sales data.

11.2 Alternative: Apply the Rule to a Region Table

If the Region dimension contains the region key and the UserAccess table maps users to regions, a DAX expression on the Region table can explicitly check whether the current user has access to each region.

VAR CurrentUser = USERPRINCIPALNAME()
VAR CurrentRegion = 'Region'[RegionName]
RETURN
    COUNTROWS(
        FILTER(
            'UserAccess',
            'UserAccess'[UserEmail] = CurrentUser
                && 'UserAccess'[Region] = CurrentRegion
        )
    ) > 0

This rule returns TRUE for a Region row when the UserAccess table contains a matching user-and-region combination. The Region table must be related correctly to the Sales table so that the permitted regions filter the sales records.

In this example, the expression assumes the tables and column names shown. Adapt the names and relationship design to your model. Test the actual filter propagation before deploying the rule.

11.3 Supporting Multiple Regions Per User

A mapping table can contain multiple rows for a single user when that user is authorized for more than one region.

The dynamic rule can permit both West and North for [email protected]. This approach supports multiple assignments without requiring a separate role for each region.

12. Using USERNAME() in Security Rules

USERNAME() returns the current user's identity in a format that depends on the environment.

12.1 Example

[UserDomain] = USERNAME()

This expression compares the UserDomain column with the identity returned by USERNAME().

12.2 USERNAME() vs. USERPRINCIPALNAME()

For dynamic RLS in the Power BI service, USERPRINCIPALNAME() is often a convenient choice when the mapping table stores principal names. Always verify the actual identity value in the environment where the report will be used.

13. DAX Functions Commonly Used in Security Rules

Not every DAX function is appropriate in every RLS expression. Use functions that are supported in the relevant security-filter context and validate their behavior in the target model.

14. Creating a Role for Multiple Regions

Suppose a regional operations team is authorized to view West and North records but not South records.

14.1 DAX Expression Using IN

[Region] IN {"West", "North"}

14.2 Alternative Using OR

[Region] = "West" || [Region] = "North"

Both expressions represent the same intended region condition for this example. The IN operator can be easier to maintain when checking membership in a list of values.

14.3 When to Use This Approach

• A small, stable group needs access to a fixed set of regions.
• A role represents a business unit with predetermined boundaries.
• The permitted values are known and do not need to be determined from each user's identity.

For many users with different combinations of regions, a dynamic mapping table is usually more scalable.

15. Restricting Data Using Department-Based Security

Security rules can also be defined around departments instead of geographic regions.

15.1 Example Employee Table

15.2 Static Department Rule

[Department] = "Finance"

This rule allows users assigned to the role to see Finance employee rows.

15.3 Dynamic Department Rule

A dynamic mapping table can associate users with departments. The rule can compare the current user to the mapping table and allow only the department records assigned to that user.

For sensitive payroll information, department-level RLS alone may not be sufficient. If users must be prevented from accessing particular columns, such as Salary, evaluate Object-Level Security (OLS) and other applicable controls.

16. Applying Security Rules to Related Tables

Security filters can propagate through model relationships. A rule on a dimension table may therefore restrict rows visible in related fact tables.

16.1 Example Model

Region
  |
  | One-to-many relationship
  v
Sales

The Region dimension contains unique region values, while the Sales table contains multiple transactions for each region.

16.2 Applying RLS to the Region Table

'Region'[RegionName] = "West"

If the Region table is correctly related to the Sales table and the filter propagates through the intended relationship, the rule can restrict the related Sales records to West.

16.3 Relationship Considerations

• Use appropriate primary and foreign keys.
• Confirm the relationship cardinality.
• Check whether relationships are active.
• Review filter direction and security-filter propagation.
• Avoid unnecessary bidirectional relationships.
• Test the security result across all related tables.

17. Managing Role Membership in Power BI Service

Creating a role in Power BI Desktop defines the filtering rules, but users must also be assigned to the role after the semantic model is published.

17.1 Publishing the Model

1. Save the Power BI Desktop file.
2. Select Publish.
3. Choose the authorized workspace.
4. Wait for the report and semantic model to be published.
5. Open the Power BI service and locate the semantic model.

17.2 Assigning Users to Roles

1. Open the workspace containing the semantic model.
2. Open the semantic model's More options menu.
3. Select Security.
4. Choose the relevant security role.
5. Add the appropriate user accounts or supported groups.
6. Save the membership changes.
7. Test the security configuration.

Available options can vary according to the service experience and the user's permissions. Role membership can be managed by authorized users with the necessary access to the semantic model.

17.3 Supported Group Considerations

Power BI supports certain Microsoft Entra security groups, distribution groups, and mail-enabled groups for RLS membership. Microsoft 365 groups are not supported for RLS role membership. Confirm the current platform requirements before choosing a group type.

18. Testing Security Rules in Power BI Desktop

Testing is essential because a DAX expression can be syntactically correct but still return unexpected data because of model relationships, role membership, or incorrect mapping values.

18.1 Using View As

1. Open the report in Power BI Desktop.
2. Select Modeling.
3. Select View as.
4. Choose the role that you want to test.
5. Where supported, enter or select the identity to test dynamic RLS.
6. Inspect the report visuals and available records.
7. Compare the results with the expected security behavior.
8. Exit the test view when validation is complete.

18.2 Testing Checklist

• Does the West role show only West records?
• Does the North role show only North records?
• Does each dynamic user receive the correct mapped data?
• Does a user with no valid mapping receive no unauthorized records?
• Are multiple permitted regions handled correctly?
• Do related tables follow the expected security filters?
• Are users assigned to the correct roles in the service?

Note that View as and Test as role have limitations in some scenarios, including DirectQuery semantic models with single sign-on enabled. In those situations, validate the security behavior using an actual appropriately permissioned user account.

19. Security Risks When Users Belong to Multiple Roles

Power BI RLS role filters are additive when a user belongs to multiple roles. This means the user can generally see the union of the rows allowed by the roles.

19.1 Example

Suppose a user belongs to two roles:

• WestRole permits West region records.
• NorthRole permits North region records.

The user can see records from both West and North. The filters do not automatically intersect to restrict the user to only records that satisfy both roles.

19.2 Best Practice

Whenever practical, design a single role that expresses the complete intended access for a user population, rather than relying on multiple overlapping roles. Test direct and group-based membership carefully because unexpected role combinations can expose more data than intended.

20. Common Errors in DAX-Based Security Rules

21. Best Practices for Creating Roles and DAX Rules

1. Use meaningful role names: Names such as WestRegion, FinanceAccess, and RegionalManagers make the model easier to maintain.
2. Apply least privilege: Give users access only to the rows and resources required for their work.
3. Prefer dynamic RLS for large user populations: A mapping table can simplify administration when access assignments change frequently.
4. Keep mapping data accurate: Remove obsolete users and correct invalid region or department assignments.
5. Validate identity formats: Ensure that stored user identifiers match the values returned by the DAX identity function.
6. Use a clean data model: Well-designed dimensions and relationships make security rules easier to reason about.
7. Test multiple roles: Confirm that combined role membership does not grant excessive access.
8. Review workspace permissions: RLS does not restrict workspace editors in the same manner as Viewer users.
9. Avoid overly complex expressions: Use readable DAX expressions that are easy to test and maintain.
10. Document security logic: Record each role's purpose, intended audience, and DAX rules.
11. Monitor performance: Complex filters and large mapping tables can affect query performance.
12. Test after changes: Revalidate rules whenever tables, relationships, identity mappings, or permissions change.

22. Hands-On Project: Regional Sales Security

Project Objective: Build a Power BI report in which each regional manager can view only the regions assigned to their account.

22.1 Requirements

• A Sales table with OrderID, Region, Product, and SalesAmount.
• A Region table containing the available regions.
• A UserAccess table mapping user email addresses to authorized regions.
• A Power BI report displaying sales by region and product.

22.2 Implementation Tasks

1. Import the required tables into Power BI Desktop.
2. Create and validate relationships between the Region and Sales tables.
3. Populate the UserAccess table with valid user and region assignments.
4. Create a role named RegionalAccess.
5. Define a dynamic DAX rule that filters the mapping data or Region table using USERPRINCIPALNAME().
6. Verify that the security filter reaches the Sales table.
7. Test different users and confirm that each receives the intended data.
8. Publish the model to an authorized Power BI workspace.
9. Assign users or supported groups to the RegionalAccess role.
10. Test access with Viewer accounts in the Power BI service.
11. Document the role, expression, relationships, and expected outcomes.

22.3 Expected Outcome

Regional managers see sales data for their authorized regions, while a user with no valid mapping does not receive regional records through the designed security path. The company maintains a shared report instead of duplicating the report for each manager.

23. Interview Questions and Answers

Q1. What is a security role in Power BI?

Answer: A security role defines rules that control which rows of data a user can access in a semantic model.

Q2. What is Row-Level Security?

Answer: RLS filters rows according to security-role expressions, allowing different users to see different subsets of the same dataset.

Q3. How do you create a role in Power BI Desktop?

Answer: Open Modeling, select Manage roles, create a new role, select the required table, define the DAX filter, and save the role.

Q4. What is a DAX-based security rule?

Answer: It is a DAX expression that determines whether rows are included in the data visible to a particular security role.

Q5. What is the difference between static and dynamic RLS?

Answer: Static RLS uses predefined filter values, while dynamic RLS determines access based on user identity or another permitted context.

Q6. What does USERPRINCIPALNAME() do?

Answer: It returns the current user's principal name and can be used to match the signed-in user against a mapping table.

Q7. Why is a user-mapping table required?

Answer: It stores the relationship between user identities and the regions, departments, or other data categories they are authorized to access.

Q8. Can one role support multiple regions?

Answer: Yes. A static rule can use IN or OR to allow multiple fixed regions, while dynamic RLS can use a mapping table containing multiple region assignments for each user.

Q9. Where are users assigned to RLS roles?

Answer: Role membership is normally configured in the Power BI service after the semantic model has been published.

Q10. What happens if a user belongs to two RLS roles?

Answer: The role filters are additive, so the user can generally see the union of the rows permitted by the roles.

Q11. Does RLS apply to workspace Admins?

Answer: No. RLS does not restrict workspace Admin, Member, or Contributor users in the same manner as Viewer users.

Q12. How can you test an RLS role?

Answer: Use View as in Power BI Desktop and the available Test as role feature in the Power BI service, followed by testing with actual user accounts when necessary.

Q13. What is the purpose of relationships in dynamic RLS?

Answer: Relationships allow the security filter to propagate from the mapping or dimension table to the relevant fact tables, provided the model is configured correctly.

Q14. Why might dynamic RLS work in Desktop but fail in the service?

Answer: The identity format, role membership, mapping-table data, or security-filter propagation may differ or be configured incorrectly.

Q15. What is the least-privilege principle?

Answer: It means granting only the access and permissions necessary for a user to perform their responsibilities.

24. Quick Revision Checklist

• Security roles define data-access rules.
• RLS restricts rows in a semantic model.
• DAX rules evaluate logical conditions for table rows.
• Manage roles is available under Modeling in Power BI Desktop.
• Static RLS uses fixed filter values.
• Dynamic RLS commonly uses USERPRINCIPALNAME() or USERNAME().
• User-mapping tables connect identities to permitted data.
• Relationships must propagate security filters correctly.
• Users are assigned to roles in the Power BI service.
• Multiple role memberships can broaden the visible data.
• Workspace Admin, Member, and Contributor roles are not restricted by RLS like Viewer roles.
• Test all roles and identity mappings before publishing to production.
• Review permissions and security rules regularly.

25. Conclusion

Creating roles and defining DAX-based security rules is an essential skill for developing secure Power BI reports. Static RLS is suitable for fixed access requirements, while dynamic RLS can use user identities and mapping tables to support larger organizations with changing access needs.

A reliable implementation requires more than writing a DAX expression. Developers must also design the model relationships, assign role membership correctly, verify workspace permissions, test actual user scenarios, and maintain accurate access mappings.

By combining well-designed security roles, readable DAX expressions, appropriate permissions, and systematic testing, Power BI developers can create reports that provide relevant business insights without exposing unauthorized data.

26. Additional Learning Resources

• Microsoft Power BI Training — JustAcademy
• Register for a Power BI Course Demo
• Official JustAcademy Website
• Microsoft Learn: Define Roles and Rules in Power BI Desktop
• Microsoft Learn: Manage Row-Level Security in Power BI
• Microsoft Learn: Row-Level Security Design Guidance